package com.ruoyi.common.utils;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import lombok.extern.slf4j.Slf4j;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ruoyi.common.enums.DataType;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

@Slf4j
@SuppressWarnings("AlibabaLowerCamelCaseVariableNaming")
public class ExcelUtils {
    public static Workbook CreateWorkbook(List<Map<String, Object>> columns, List<List<Object>> rows) {
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("Sheet1");
        Row row_0 = sheet.createRow(0);
        for (int i = 0; i < columns.size(); i++) {
            Map<String, Object> column = columns.get(i);
            Cell cell = row_0.createCell(i);
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);

            Font font = wb.createFont();
            font.setBold(true);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(column.get("name").toString());
        }
        for (int j = 0; j < rows.size(); j++) {
            List<Object> row = rows.get(j);
            Row row_j = sheet.createRow(j + 1);
            for (int k = 0; k < row.size(); k++) {
                Cell cell = row_j.createCell(k);
                CellStyle cellStyle = wb.createCellStyle();
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(row.get(k).toString());
            }
        }
        return wb;
    }

    public static Workbook CreateEventWorkbook(Workbook wb, List<String> columns, String sheetName, List<List<Map<String, String>>> rows,String sn) {
        //生成产品活跃状态
        Sheet sheet2 = wb.createSheet(sheetName + "活跃状态");
        columns.set(0, "时间");
        if (columns.size() >= 2) {
            sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, columns.size() - 1));
        }
        Row row_0 = sheet2.createRow(0);
        Cell cell0 = row_0.createCell(0);
        CellStyle cellStyle0 = wb.createCellStyle();
        cellStyle0.setAlignment(HorizontalAlignment.CENTER);
        Font font = wb.createFont();
        font.setBold(true);
        cellStyle0.setFont(font);
        cell0.setCellStyle(cellStyle0);
        cell0.setCellValue(sn);
        Row row_21 = sheet2.createRow(1);
        for (int i = 0; i < columns.size(); i++) {
            String column = columns.get(i);
            Cell cell = row_21.createCell(i);
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            Font font2 = wb.createFont();
            font2.setBold(true);
            cellStyle.setFont(font2);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(column);
        }

        List<List<Map<String, String>>> activeRows = new ArrayList<>();
        for (List<Map<String, String>> list : rows) {
            List<Map<String, String>> mapList = new ArrayList<>();
            for (Map<String, String> map : list) {
                if ("time".equals(map.get("name"))) {
                    mapList.add(map);
                    continue;
                }
                if (map.get("name").contains("__active")) {
                    mapList.add(map);
                }
            }
            activeRows.add(mapList);
        }
        for (int j = 0; j < activeRows.size(); j++) {
            List<Map<String, String>> row = activeRows.get(j);
            Row row_j = sheet2.createRow(j + 2);
            for (int k = 0; k < row.size(); k++) {
                Map<String, String> map = row.get(k);
                Cell cell = null;
                if (k == 0) {
                    cell = row_j.createCell(k);
                    cell.setCellValue(map.get("data"));
                    continue;
                }
                if (!map.get("name").contains("__active")) {
                    continue;
                }
                if (StrUtil.isBlank(Convert.toStr(map.get("data")))) {
                    cell = row_j.createCell(k);
                    cell.setCellValue(map.get("data"));
                    continue;
                }
                String status = null;
                if (StrUtil.isBlank(Convert.toStr(map.get("data")))) {
                    status = "";
                }
                if (StrUtil.equals(Convert.toStr(map.get("data")), "1")) {
                    status = "在线";
                } else {
                    status = "离线";
                }
                cell = row_j.createCell(k);
                cell.setCellValue(status);
            }
        }
        return wb;
    }

    /**
     * 报表Excel的生成
     *
     * @return
     */
    public static Workbook CreateWorkbook(Workbook wb, List<String> columns, String sheetName, List<List<Map<String, String>>> rows,String sn) {
        Sheet sheet = wb.createSheet(sheetName);
        if (columns.size() >= 2) {
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columns.size() - 1));
        }
        Row row_0 = sheet.createRow(0);
        Cell cell0 = row_0.createCell(0);
        CellStyle cellStyle0 = wb.createCellStyle();
        cellStyle0.setAlignment(HorizontalAlignment.CENTER);
        Font font = wb.createFont();
        font.setBold(true);
        cellStyle0.setFont(font);
        cell0.setCellStyle(cellStyle0);
        cell0.setCellValue(sn);
        Row row_1 = sheet.createRow(1);
        for (int i = 0; i < columns.size(); i++) {
            String column = columns.get(i);
            Cell cell = row_1.createCell(i);
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            Font font2 = wb.createFont();
            font2.setBold(true);
            cellStyle.setFont(font2);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(column);
        }

        for (int j = 0; j < rows.size(); j++) {
            List<Map<String, String>> row = rows.get(j);
            Row row_j = sheet.createRow(j + 2);
            for (int k = 0; k < row.size(); k++) {
                Cell cell = row_j.createCell(k);
                Map<String, String> map = row.get(k);
                if (k == 0) {
                    cell.setCellValue(map.get("data"));
                    continue;
                }
                if (map.get("name").contains("__active")) {
                    continue;
                }
                if (StrUtil.isBlank(Convert.toStr(map.get("data")))) {
                    cell.setCellValue(map.get("data"));
                    continue;
                }
                String dataType = map.get("dataType");
                if (dataType.equals(DataType.BOOL.getLabel())) {
                    cell.setCellValue(Boolean.parseBoolean(Convert.toStr(map.get("data"))));
                } else if (dataType.equals(DataType.STRING.getLabel())) {
                    cell.setCellValue(Convert.toStr(map.get("data")));
                } else {
                    if (dataType.equals(DataType.INT32.getLabel())) {
                        cell.setCellValue(Convert.toInt(map.get("data")));
                    } else if (dataType.equals(DataType.INT64.getLabel())) {
                        cell.setCellValue(Convert.toLong(map.get("data")));
                    } else {
                        cell.setCellValue(Convert.toDouble(map.get("data")));
                    }
                }

            }
        }
        return wb;
    }

    /**
     * 报表Excel的生成
     *
     * @return
     */
    public static Workbook CreateWorkbookDetail(Workbook wb, List<Map<String, String>> columns, String sheetName, List<List<Map<String,String>>> rows) {
        Sheet sheet = wb.createSheet(sheetName);
        Row row_0 = sheet.createRow(0);
        for (int i = 0; i < columns.size(); i++) {
            Map<String, String> column = columns.get(i);
            Cell cell = row_0.createCell(i);
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            Font font = wb.createFont();
            font.setBold(true);
            cellStyle.setFont(font);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(column.get("name"));
        }

        for (int j = 0; j < rows.size(); j++) {
            List<Map<String,String>> row = rows.get(j);
            Row row_j = sheet.createRow(j + 1);
            for (int k = 0; k < row.size(); k++) {
                Cell cell = row_j.createCell(k);
                Map<String,String> valueMap = row.get(k);
                String dataType = valueMap.get("dataType");
                if ("int".equals(dataType)){
                    cell.setCellValue(Convert.toInt(valueMap.get("data")));
                } if ("decimal".equals(dataType)){
                    cell.setCellValue(Convert.toDouble(valueMap.get("data")));
                } else {
                    cell.setCellValue(valueMap.get("data"));
                }

            }
        }
        return wb;
    }

    /**
     * 报表Excel的生成
     *
     * @return
     */
    public static Workbook CreateWorkbookEvent(Workbook wb, List<Map<String, String>> columns, String sheetName, List<List<Map<String,String>>> rows,String sn) {
        Sheet sheet = wb.createSheet(sheetName);
        if (columns.size() >= 2) {
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columns.size() - 1));
        }
        Row row_0 = sheet.createRow(0);
        Cell cell0 = row_0.createCell(0);
        CellStyle cellStyle0 = wb.createCellStyle();
        cellStyle0.setAlignment(HorizontalAlignment.CENTER);
        Font font = wb.createFont();
        font.setBold(true);
        cellStyle0.setFont(font);
        cell0.setCellStyle(cellStyle0);
        cell0.setCellValue(sn);
        Row row_1 = sheet.createRow(1);
        for (int i = 0; i < columns.size(); i++) {
            Map<String, String> column = columns.get(i);
            Cell cell = row_1.createCell(i);
            CellStyle cellStyle = wb.createCellStyle();
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            Font font2 = wb.createFont();
            font2.setBold(true);
            cellStyle.setFont(font2);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(column.get("name"));
        }

        for (int j = 0; j < rows.size(); j++) {
            List<Map<String,String>> row = rows.get(j);
            Row row_j = sheet.createRow(j + 2);
            for (int k = 0; k < row.size(); k++) {
                Cell cell = row_j.createCell(k);
                Map<String,String> valueMap = row.get(k);
                String dataType = valueMap.get("dataType");
                if ("int".equals(dataType)){
                    cell.setCellValue(Convert.toInt(valueMap.get("data")));
                } if ("decimal".equals(dataType)){
                    cell.setCellValue(Convert.toDouble(valueMap.get("data")));
                } else {
                    cell.setCellValue(valueMap.get("data"));
                }

            }
        }
        return wb;
    }

    /**
     * 生成概况
     *
     * @param wb
     * @param sheetName
     * @param deviceData               产品数据
     * @param lineDateNotDate    列表数据
     * @param lineAlarmDate      报警数据
     * @param linePointCheckDate 点检数据
     * @return
     */
    public static Workbook CreateSheet0(Workbook wb, String sheetName, List<List<String>> deviceData, List<List<String>> lineDateNotDate, List<List<String>> lineAlarmDate, List<List<String>> linePointCheckDate) {
        Sheet sheet = wb.createSheet(sheetName);
//        if (rows.size() > 3) {
//            sheet.addMergedRegion(new CellRangeAddress(rows.size(), rows.size() + 1, 0, 0));
//        }
        int lineDateNotDataSize = CollUtil.isEmpty(lineDateNotDate) ? 0 : lineDateNotDate.size() + 1;
        int lineAlarmDateSize = CollUtil.isEmpty(lineAlarmDate) ? 0 : lineAlarmDate.size() + 1;
        int linePointCheckDateSize = CollUtil.isEmpty(linePointCheckDate) ? 0 : linePointCheckDate.size() + 1;

        if (CollUtil.isNotEmpty(lineDateNotDate)) {
            if (lineDateNotDate.size() > 1){
                sheet.addMergedRegion(new CellRangeAddress(1, lineDateNotDate.size(), 0, 0));
            }
            for (int i = 0; i < lineDateNotDate.size(); i++) {
                List<String> row = lineDateNotDate.get(i);
                Row row1 = sheet.createRow(i + 1);
                for (int j = 0; j < row.size(); j++) {
                    Cell cell = row1.createCell(j);
                    cell.setCellValue(row.get(j));
                }
            }
        }
        if (CollUtil.isNotEmpty(lineAlarmDate)) {
            for (int i = 0; i < lineAlarmDate.size(); i++) {
                List<String> row = lineAlarmDate.get(i);
                Row row1 = sheet.createRow(lineDateNotDataSize + 1 + i);
                for (int j = 0; j < row.size(); j++) {
                    Cell cell = row1.createCell(j);
                    cell.setCellValue(row.get(j));
                }
            }
        }
        if (CollUtil.isNotEmpty(linePointCheckDate)){
            for (int i = 0; i < linePointCheckDate.size(); i++) {
                List<String> row = linePointCheckDate.get(i);
                Row row1 = sheet.createRow(lineDateNotDataSize + lineAlarmDateSize + 1 + i);
                for (int j = 0; j < row.size(); j++) {
                    Cell cell = row1.createCell(j);
                    cell.setCellValue(row.get(j));
                }
            }
        }

        if (CollUtil.isNotEmpty(deviceData)){
            for (int i = 0; i < deviceData.size(); i++) {
                List<String> row = deviceData.get(i);
                Row row1 = sheet.createRow(lineDateNotDataSize + lineAlarmDateSize + linePointCheckDateSize + 1 + i);
                for (int j = 0; j < row.size(); j++) {
                    Cell cell = row1.createCell(j);
                    cell.setCellValue(row.get(j));
                }
            }
        }

//        for (int j = 0; j < rows.size(); j++) {
//            List<String> row = rows.get(j);
//            Row row_j = null;
//            if (j <= 1) {
//                row_j = sheet.createRow(j + 1);
//            } else {
//                row_j = sheet.createRow(j + 2);
//            }
//
//            for (int k = 0; k < row.size(); k++) {
//                Cell cell = row_j.createCell(k);
//                cell.setCellValue(row.get(k));
//
//            }
//        }
        return wb;
    }

    private static String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:
                Double doubleValue = cell.getNumericCellValue();
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING:
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:
                break;
            case FORMULA:
                returnValue = cell.getCellFormula();
                break;
            case ERROR:
                break;
            default:
                break;
        }
        return returnValue;
    }

    public static List<JSONObject> load(String filepath) {
        List<JSONObject> items = null;
        try {
            File excelFile = new File(filepath);
            FileInputStream fis = new FileInputStream(excelFile);
            Workbook workbook = null;
            String ext = filepath.substring(filepath.lastIndexOf("."));
            if (".xls".equals(ext)) {
                workbook = new HSSFWorkbook(fis);
            } else if (".xlsx".equals(ext)) {
                workbook = new XSSFWorkbook(fis);
            }
            Sheet sheet = workbook.getSheetAt(0);
            Row header = sheet.getRow(0);
            List<String> fields = new ArrayList<>();
            for (int i = 0; i < header.getLastCellNum(); i++) {
                fields.add(convertCellValueToString(header.getCell(i)));
            }
            items = new ArrayList<>();
            Integer rowCount = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rowCount; i++) {
                Row row = sheet.getRow(i);
                if (null == row) {
                    continue;
                }
                JSONObject item = new JSONObject();
                for (int j = 0; j < fields.size(); j++) {
                    String key = fields.get(j);
                    String value = convertCellValueToString(row.getCell(j));
                    item.put(key, value);
                }
                items.add(item);
            }
        } catch (Exception e) {
            log.info("",e);
            StackTraceElement[] traces = e.getStackTrace();
            for (StackTraceElement trace : traces) {
                log.debug(trace.toString());
            }
        }
        return items;
    }
}
